﻿using System.Text;

namespace Sucdri.Infrastructure.Core.DbUtility
{
   internal static class PageHelper
   {
      /// <summary>
      /// 获取分页Sql
      /// </summary>
      /// <param name="cmdText">传入的SQL Query</param>
      /// <param name="pageIndex">当前页面</param>
      /// <param name="pageSize">每页数量</param>
      /// <param name="orderBy">排序字段，根据“，”分隔</param>
      /// <returns></returns>
      internal static string GeneratePagingSql(string cmdText, int pageIndex, int pageSize, string orderBy)
      {
         int startRecord = pageIndex <= 0 ? 1 : (pageIndex - 1)*pageSize + 1;
         var pagingSql = new StringBuilder("WITH t AS (");
         pagingSql.AppendFormat("SELECT ROW_NUMBER() OVER(ORDER BY {0}) AS RowNumber", orderBy)
            .AppendFormat(",* FROM ({0}) AS tb) ", cmdText)
            .AppendFormat("SELECT * FROM t WHERE RowNumber BETWEEN {0} AND {1};", startRecord,
                          (startRecord + pageSize - 1))
            .AppendFormat("SELECT COUNT(*) FROM ({0}) temp;", cmdText);
         return pagingSql.ToString();
      }
   }
}